package cn.com.scitc.licenseplateback.dao;

import cn.com.scitc.licenseplateback.model.MembershipInformationEntity;
import cn.com.scitc.licenseplateback.model.RegisterEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;


@Repository
public interface MembershipInformationDao extends JpaRepository<MembershipInformationEntity, Integer> {

    /**
     * 会员注册
     *
     * @param registerEntity 会员实例
     * @return 1
     */
    @Modifying
    @Transactional
    @Query(value = "insert into MembershipInformation (Name,IDNumber,LicensePlateNumber,CellPhoneNumber,RegisterTime,Password,Stal) values(:#{#registerEntity.Name},:#{#registerEntity.Id},:#{#registerEntity.PlateNumber},:#{#registerEntity.CellPhoneNumber},:#{#registerEntity.RegisterTime},:#{#registerEntity.Password},:#{#registerEntity.Stal})", nativeQuery = true)
    int addMember(@Param("registerEntity") RegisterEntity registerEntity);

    /**
     * 根据会员身份证号进行查询
     *
     * @param IdNumber
     * @return
     */
    @Query(value = "select m from MembershipInformationEntity m where m.idNumber = :IdNumber")
    MembershipInformationEntity findbyMember(String IdNumber);

    /**
     * 根据会员的姓名和车牌号进行会员信息查询
     *
     * @param name
     * @param plateNumber
     * @return
     */
    @Query(value = "select m from MembershipInformationEntity m where m.name = :name and m.licensePlateNumber like %:plateNumber%")
    MembershipInformationEntity findByNameAndPlateNumber(String name, String plateNumber);

    /**
     * 根据会员的手机号进行信息查询 ， 用于手机号登录
     *
     * @param cellPhoneNumber
     * @return
     */
    @Query(value = "select m from MembershipInformationEntity m where m.cellPhoneNumber = :cellPhoneNumber")
    MembershipInformationEntity findByMemberPhoneNumber(String cellPhoneNumber);

    /**
     * 处理会员车辆车牌号录入
     *
     * @param PlateNumber
     * @param IDNumber
     * @return
     */
    @Modifying
    @Transactional
    @Query(value = "update MembershipInformation set MembershipInformation.LicensePlateNumber = CONCAT_WS(',',MembershipInformation.LicensePlateNumber,:PlateNumber) where IDNumber = :IDNumber", nativeQuery = true)
    int updateAddPlateNumber(String PlateNumber, String IDNumber);

    /**
     * 根据用户传入的 新手机号、身份证号
     * 以身份证号为索引条件
     * 更新旧手机号为新手机号
     * @param newPhoneNumber 新手机号
     * @param IDNumber 身份证号
     * @return 更新条数
     */
    @Modifying
    @Transactional
    @Query(value = "update MembershipInformation set CellPhoneNumber = :newPhoneNumber where IDNumber = :IDNumber", nativeQuery = true)
    int updateUserPhoneNumber(String newPhoneNumber, String IDNumber);


}
